import { Inject, Provide } from '@midwayjs/core';
import { query, transaction } from '../dbConnPool/mariadb'; // 引入查询函数
import { UserDTO, AdminUpdateUserDTO, UserIdentityDTO } from '../dto/user';
import { PageDTO } from '../dto/page';
//import { Caching } from '@midwayjs/cache-manager';
import { Context } from '@midwayjs/koa';
//import { uuidv7 } from 'uuidv7';
import { CachingFactory, MidwayCache } from '@midwayjs/cache-manager';
import { InjectClient } from '@midwayjs/core';
@Provide()
export class UserDao {
  @Inject()
  ctx: Context;
  //通过id查找用户
  // @Caching('redis', ctx => {
  //   if (ctx.methodArgs.length > 0) {
  //     return `userId:${ctx.methodArgs[0]}`;
  //   }
  //   return null;
  // })
  async findUserById(userId: UserDTO['userId']) {
    const sql = `
    SELECT
    u.user_id AS userId,
    u.department_id AS departmentId,
    d.name AS departmentName,
    u.account,
    u.name,
    i.item_label AS identity, 
    s.item_label AS sex, 
    u.email,
    u.phone,
    u.authentication_flag AS authenticationFlag,
    u.director_flag AS directorFlag,
    u.is_admin AS role,
    u.password AS passwordHash
FROM
    user u
LEFT JOIN
    department d ON u.department_id = d.id
LEFT JOIN
    sys_dict_item i ON u.identity = i.item_code AND i.dict_code = 'IDENTITY'
LEFT JOIN
    sys_dict_item s ON u.sex = s.item_code AND s.dict_code = 'SEX'
WHERE
        u.user_id = ? 
        AND u.valid_flag = 1  
    `;
    const sqlParams = [userId];
    return (await query(sql, sqlParams))[0];
  }

  //通过账号邮箱密码查找用户
  async findAccount(field: string, value: string) {
    const sql = `
      SELECT
        COUNT(*) AS count
      FROM 
        user
      WHERE 
        ${field} = ?
    `;
    const sqlParams = [value];
    const result = await query(sql, sqlParams, false); // 设置 throwOnEmptyResult 为 false
    return result[0].count;
  }

  //删除用户
  async delUser(userId: UserDTO['userId']) {
    const sql = `
    UPDATE
        user
      SET
        valid_flag = 0
      WHERE
        user_id = ?
    `;
    const sqlParams = [userId];
    return await query(sql, sqlParams);
  }

  //上传验证凭证图片
  async uploadImg(url: string) {
    const userId = this.ctx.state.user.userId;
    const sql1 = `
    REPLACE INTO
        authentication( user_id, link, created_at)
      VALUES
        ( ?, ?, NOW())
    `;
    const sqlParams1 = [userId, url];
    await query(sql1, sqlParams1);
    const sql2 = `
    UPDATE
        user
      SET
        authentication_flag = 2
      WHERE
        user_id = ?
    `;
    const sqlParams2 = [userId];
    await query(sql2, sqlParams2);
    return true;
  }
  //更新用户信息
  @InjectClient(CachingFactory, 'redis')
  cache1: MidwayCache; //redis缓存
  async updateUserInfo(
    userId: UserDTO['userId'],
    updateUserDto: AdminUpdateUserDTO
  ) {
    return transaction(async conn => {
      const setClause = [];
      const sqlParams = [];
      for (const [key, value] of Object.entries(updateUserDto)) {
        if (key === 'userId' || key === 'remakePassword') {
          continue;
        }
        const snakeKey = this.camelToSnake(key);
        setClause.push(`${snakeKey} = ?`);
        sqlParams.push(value);
      }
      sqlParams.push(userId);
      const sql = `
      UPDATE
        user
      SET
        ${setClause.join(', ')}
        WHERE
        user_id = ?
    `;
      await query(sql, sqlParams, true, conn);
      if (updateUserDto.authenticationFlag) {
        let authenticationValidFlag = null;
        if (updateUserDto.authenticationFlag === 1) {
          authenticationValidFlag = 1;
        } else if (updateUserDto.authenticationFlag === 2) {
          authenticationValidFlag = 2;
        } else if (updateUserDto.authenticationFlag === 3) {
          authenticationValidFlag = 0;
        }
        const sql1 = `
        UPDATE
          authentication 
        SET
          valid_flag = ?
        WHERE
          user_id = ?
      `;
        const sqlParams1 = [authenticationValidFlag, userId];
        await query(sql1, sqlParams1, true, conn);
      }
      await this.cache1.del(`userId:${userId}`);
      return true;
    });
  }

  //管理员获取用户验证身份图片
  async getUserIdentityImg(pageDto: PageDTO) {
    const { pageNum, pageSize, type } = pageDto;
    return transaction(async conn => {
      const sql = `
    SELECT
      u.user_id AS userId,
      u.name,
      i.item_label AS identity,
      s.item_label AS sex,
      d.name AS departmentName,
      a.link AS imgUrl,
      a.text,
      a.valid_flag AS validFlag,
      a.created_at AS createdAt
    FROM
      user u
    LEFT JOIN
      department d ON u.department_id = d.id
    LEFT JOIN
      sys_dict_item i ON u.identity = i.item_code AND i.dict_code = 'IDENTITY'
    LEFT JOIN
      sys_dict_item s ON u.sex = s.item_code AND s.dict_code = 'SEX'
    LEFT JOIN
      authentication a ON u.user_id = a.user_id
    WHERE
      u.valid_flag = 1 AND a.valid_flag = ?
    ORDER BY
      u.created_at DESC
    LIMIT
      ?,?
    `;
      const sqlParams = [type, (pageNum - 1) * pageSize, pageSize];
      const list = await query(sql, sqlParams, false, conn);
      const sql1 = `
      SELECT
      COUNT(*) AS total
    FROM
    authentication a
    WHERE
      a.valid_flag = ?
    `;
      const sqlParams1 = [type];
      const total = await query(sql1, sqlParams1, false, conn);
      return { total: total[0].total, list };
    });
  }
  //管理员审批用户验证图片
  async approveUserIdentityImg(userIdentityDto: UserIdentityDTO) {
    const { userId, validFlag } = userIdentityDto;
    const adminId = this.ctx.state.user.userId;
    if (!userIdentityDto.text || userIdentityDto.validFlag !== 0) {
      userIdentityDto.text = '';
    }
    return transaction(async conn => {
      const sql = `
    UPDATE
      authentication 
    SET
      valid_flag = ?,
      text = ?,
      admin_id = ?
    WHERE
      user_id = ?
  `;
      const sqlParams = [validFlag, userIdentityDto.text, adminId, userId];
      await query(sql, sqlParams, true, conn);
      let authenticationFlag = null;
      if (validFlag === 0) {
        authenticationFlag = 3;
      } else if (validFlag === 1) {
        authenticationFlag = 1;
      } else if (validFlag === 2) {
        authenticationFlag = 2;
      }
      const sql1 = `
    UPDATE
      user 
    SET
      authentication_flag = ?
    WHERE
      user_id = ?
  `;
      const sqlParams1 = [authenticationFlag, userId];
      await query(sql1, sqlParams1, true, conn);
      return true;
    });
  }
  //管理员获取所有用户信息
  async getAllUser(allUserDto: PageDTO) {
    const { pageNum, pageSize, type } = allUserDto;
    return transaction(async conn => {
      const sql = `
    SELECT
      u.user_id AS userId,
      u.name,
      i.item_label AS identity,
      s.item_label AS sex,
      d.name AS departmentName,
      u.email,
      u.phone,
      u.authentication_flag AS authenticationFlag,
      u.valid_flag AS validFlag,
      u.created_at AS createdAt,
      a.link AS imgUrl,
      a.valid_flag AS imgValidFlag,
      u.is_admin AS role
    FROM
      user u
    LEFT JOIN
      department d ON u.department_id = d.id
    LEFT JOIN
      sys_dict_item i ON u.identity = i.item_code AND i.dict_code = 'IDENTITY'
    LEFT JOIN
      sys_dict_item s ON u.sex = s.item_code AND s.dict_code = 'SEX'
    LEFT JOIN
      authentication a ON u.user_id = a.user_id
      WHERE
      u.valid_flag = ?
    ORDER BY
      u.created_at DESC
    LIMIT
      ?,?
    `;
      const sqlParams = [type, (pageNum - 1) * pageSize, pageSize];
      const list = await query(sql, sqlParams, false, conn);
      const sql1 = `
    SELECT
      COUNT(*) AS total
    FROM
      user
    WHERE
      valid_flag = ?
    `;
      const sqlParams1 = [type];
      const total = await query(sql1, sqlParams1, false, conn);
      return { total: total[0].total, list };
    });
  }
  // 工具函数：驼峰命名转下划线命名
  camelToSnake(str: string): string {
    return str.replace(/([a-z0-9])([A-Z])/g, '$1_$2').toLowerCase();
  }
}
